# -*- coding:utf-8 -*-

import pymysql
#pymysql.install_as_MySQLdb()  # 手动指定将MySQLdb转给pymysql处理
import tormysql
import tornado.util
import tornado.gen
import tornado.log
import datetime
import json
from service.utility import db_data_tool
import time


class Mysql:
    table_description = {}  # 数据库表/字段描述信息

    def __init__(
            self, host=None, port=3306, user=None, passwd='', db='', charset='utf8',max_connections=3
            , idle_seconds=7200, wait_connection_timeout=3, debug=False):
        self.debug = debug
        self.conn_pool = tormysql.ConnectionPool(
            host=host,
            port=port,
            user=user,
            passwd=passwd,
            db=db,
            charset=charset,
            max_connections=max_connections,
            idle_seconds=idle_seconds,
            wait_connection_timeout=wait_connection_timeout
        )
        self.db_name = db

    async def refresh_table_description(self):
        for line in await self.query('show tables'):
            table = line['Tables_in_' + self.db_name]
            field_info = await self.query('DESCRIBE ' + table)
            self.table_description[table] = db_data_tool.dict_list2ddict(field_info, parse_col_name='Field')

    @staticmethod
    def row_to_obj(row, cur, fields_s2json: tuple=(), format_date=True, null2s=True):
        """Convert a SQL row to an object supporting dict and attribute access."""
        obj = tornado.util.ObjectDict()
        for val, desc in zip(row, cur.description):
            if isinstance(val, datetime.datetime) and format_date:
                obj[desc[0]] = val.strftime('%Y-%m-%d %H:%M:%S')
            elif isinstance(val, datetime.timedelta):
                obj[desc[0]] = str(val)
            elif isinstance(val, type(None)) and null2s:
                obj[desc[0]] = ''
            elif desc[0] in fields_s2json:
                obj[desc[0]] = json.loads(val)
            else:
                obj[desc[0]] = val
        return obj

    async def get_connection(self):
        return await self.conn_pool.Connection()

    async def _execute(self, cursor, stmt, *args):
        return await mysql_executor(cursor, stmt, *args, is_debug=self.debug)


    async def execute(self, stmt, *args, cursor=None):
        if cursor:
            return await self._execute(cursor, stmt, args)
        async with await self.get_connection() as conn:
            async with conn.cursor() as cursor:
                return await self._execute(cursor, stmt, args)

    async def execute_many(self, stmt_args_list: list):
        async with await self.get_connection() as conn:
            async with conn.cursor() as cursor:
                for stmt, *args in stmt_args_list:
                    await self._execute(cursor, stmt, args)

    async def query(self, stmt, *args, fields_s2json: tuple=(), cursor=None, format_date=True, null2s=True):
        if cursor:
            await self._execute(cursor, stmt, args)
            res = self.fetch_all(cursor)
        else:
            async with await self.get_connection() as conn:
                async with conn.cursor() as cursor:
                    await self._execute(cursor, stmt, args)
                    res = self.fetch_all(cursor)
        res = [
            self.row_to_obj(row, cursor, fields_s2json=fields_s2json, format_date=format_date, null2s=null2s)
            for row in res
        ]
        return res

    async def query_one(self, stmt, *args, fields_s2json: tuple=()):
        result = await self.query(stmt, fields_s2json=fields_s2json, *args)
        if result:
            if len(result) > 1:
                raise Exception(f"Expected 1 result, got len(results), stmt: {stmt}, args: {args}")
        else:
            return {}
        return result[0]

    async def query_by_dict(
            self, table: str,
            col: tuple=('*',),
            where: dict=None,
            where_excepted: tuple = (None, ''),
            where_logic: str='and',
            limit: str='',
            order_by: str='',
            fields_s2json: tuple = ()
    ):
        where = {} if not where else where
        stmt, args = self.by_dict_to_query_sql(
            table=table,
            column=col,
            where=where,
            where_logic=where_logic,
            limit=limit,
            order_by=order_by,
            where_excepted=where_excepted
        )
        return await self.query(stmt, *args, fields_s2json=fields_s2json)

    async def query_one_by_dict(
            self, table: str,
            col: tuple=('*',),
            where: dict=...,
            where_logic: str='and',
            order_by: str='',
            where_excepted: tuple=(None, '')
    ):
        stmt, args = self.by_dict_to_query_sql(
            table=table, column=col, where=where, where_logic=where_logic, order_by=order_by, where_excepted=where_excepted
        )
        return await self.query_one(stmt, *args)

    @staticmethod
    def fetch_all(cursor):
        res = cursor.fetchall()
        return res

    # def add_query_method(self, table: str, field: str) -> str:
    #     t: str = self.table_description[table][field]['Type']
    #     if t.startswith('varchar'):
    #         add = "ifnull({field}, '') as {field}"
    #     elif t.startswith('datetime'):
    #         add = ""

    @staticmethod
    def by_dict_to_query_sql(
            table: str, column: tuple,
            where: dict,
            where_excepted: tuple,
            where_logic: str='and',
            limit: str='',
            order_by: str=''
    ):
        s = 'select '
        for col in column:
            s += f'{col},'
        s = s[0:-1] + f' from {table}'
        filter_k = []
        filter_v = []
        for k, v in where.items():  # 去除空的where条件
            if v not in where_excepted:
                filter_k.append(f'{k}=%s')
                filter_v.append(v)
        if filter_k:
            s += ' where '
        filter_k = f' {where_logic} '.join(filter_k)
        s += filter_k
        if limit:
            s += ' limit %s'
            filter_v.append(limit)
        if order_by:
            s += f' order by {order_by}'
        return [s, filter_v]

    async def insert_by_dict(self, table: str, data: dict, cursor=None, get_last_id: bool=False):
        stmt, args = self.by_dict_to_insert_sql(table, data)
        if cursor:
            r = await self._execute(cursor, stmt, args)
            if get_last_id:
                await self._execute(cursor, "SELECT LAST_INSERT_ID()")
                r = cursor.fetchone()[0]
        else:
            async with await self.get_connection() as conn:
                async with conn.cursor() as cursor:
                    r = await self._execute(cursor, stmt, args)
                    if get_last_id:
                        await self._execute(cursor, "SELECT LAST_INSERT_ID()")
                        r = cursor.fetchone()[0]
        return r

    async def insert_by_dict_list(self, table: str, data_list: list, cursor=None):
        if cursor:
            for d in data_list:
                stmt, args = self.by_dict_to_insert_sql(table, d)
                await self._execute(cursor, stmt, args)
        else:
            async with await self.get_connection() as conn:
                async with conn.cursor() as cursor:
                    for d in data_list:
                        stmt, args = self.by_dict_to_insert_sql(table, d)
                        await self._execute(cursor, stmt, args)

    @staticmethod
    def by_dict_to_insert_sql(table: str, data: dict={}):
        s = f'insert into {table} ('
        for col in data.keys():
            s = s + f'{col},'
        s = s[0:-1] + ') values ('
        filter_k = []
        filter_v = []
        for k, v in data.items():
            filter_k.append(f'%s')
            if isinstance(v, (list, dict, tuple)):
                v = json.dumps(v, ensure_ascii=False)
            filter_v.append(v)
        filter_k = ','.join(filter_k)
        s = s + filter_k + ')'
        return [s, filter_v]

    async def update_by_dict(self, table: str, colv: dict={}, where: dict={}, where_logic: str='and', limit: str= ''):
        stmt, args = self.by_dict_to_update_sql(table, colv=colv, where=where, where_logic=where_logic, limit=limit)
        async with await self.get_connection() as conn:
            async with conn.cursor() as cursor:
                return await self._execute(cursor, stmt, args)

    @staticmethod
    def by_dict_to_update_sql(table: str, colv: dict={}, where: dict={}, where_logic: str='and', limit: str=''):
        s = f'update {table} set '
        tlist = []
        stmt_args = []
        for col, value in colv.items():
            tlist.append(f'{col}=%s')
            if isinstance(value, (list, dict, tuple)):
                value = json.dumps(value, ensure_ascii=False)
            stmt_args.append(value)
        s += ','.join(tlist)
        tlist.clear()
        if where:
            s += ' where '
            for col, value in where.items():
                tlist.append(f'{col}=%s')
                if isinstance(value, (list, dict, tuple)):
                    value = json.dumps(value, ensure_ascii=False)
                stmt_args.append(value)
            s += f' {where_logic} '.join(tlist)
        if limit:
            s += ' limit %s'
            stmt_args.append(limit)
        return [s, stmt_args]

    async def delete_by_dict(self, table: str, where: dict={}, where_logic: str='and', limit: str=''):
        stmt, args = self.by_dict_to_delete_sql(table, where=where, where_logic=where_logic, limit=limit)
        async with await self.get_connection() as conn:
            async with conn.cursor() as cursor:
                return await self._execute(cursor, stmt, args)

    async def delete_by_dicts(self, table: str, where_list: tuple=(), where_logic: str='and', limit: str=''):
        async with await self.get_connection() as conn:
            async with conn.cursor() as cursor:
                for where in where_list:
                    stmt, args = self.by_dict_to_delete_sql(table, where=where, where_logic=where_logic, limit=limit)
                    await self._execute(cursor, stmt, args)

    @staticmethod
    def by_dict_to_delete_sql(table: str, where: dict={}, where_logic: str='and', limit: str=''):
        s = f'delete from {table}'
        tlist = []
        stmt_args = []
        s += ' where '
        for col, value in where.items():
            tlist.append(f'{col}=%s')
            stmt_args.append(value)
        s += f' {where_logic} '.join(tlist)
        if limit:
            s += ' limit %s'
            stmt_args.append(limit)
        return [s, stmt_args]

    async def get_transaction_connection(self):
        return Transaction('connection', (await self.get_connection()))

    async def get_transaction_cursor(self):
        return Transaction('cursor', (await self.get_connection()))


async def mysql_executor(cursor, stmt, *args, is_debug=None):
    if is_debug:
        tornado.log.app_log.info(f'execute_sql: {stmt}, \n\rargs: {args}')
    res = await cursor.execute(stmt, *args)
    return res


class Transaction:
    def __init__(self, sign: str, conn):
        self.conn = conn
        self.cursor = None
        if sign == 'cursor':
            self.cursor = conn.cursor()

    async def __aenter__(self):
        return self.cursor if self.cursor else self.conn

    async def __aexit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            await self.conn.rollback()
        else:
            await self.conn.commit()
            await self.conn.close()


async def _test_mysql():
    db = Mysql(
        host='localhost',
        port=3306,
        user='root',
        passwd='DCtest_123.',
        db='tdp',
    )
    async with await db.get_transaction_cursor() as cursor:
        res = await db.query('select * from tdp_user', cursor=cursor)
    print(res)
    async with await db.get_transaction_connection() as conn:
        res = await db.query('select * from tdp_user', cursor=conn.cursor())
    print(res)
    # res = await db.query_by_dict(table='tdp_user', col=('*',), where={'id': 1})
    # print(res)
    # res = await db.insert_one_by_dict('job', **{'user_id': 0, 'create_way': 'web', 'name': '自动化测试', 'config_name': '209环境配置', 'status': 3})
    # print(res)
    # res = await db.update_by_dict(table='job', colv={'user_id':5, 'status':5}, where={'id':172, 'user_id': 0})
    # print(res)
    # res = await db.delete_by_dict(table='job', where={'user_id':5, 'status':5})
    # print(res)

    db.conn_pool.close()

if __name__ == '__main__':
    from tornado.ioloop import IOLoop

    tornado.ioloop.IOLoop().run_sync(_test_mysql)
